﻿namespace LinqToModel
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Entities.Employees;

    /// <summary>
    /// TODO: Update summary.
    /// </summary>
    public static class LinqQueries
    {
        #region Constructors

        static LinqQueries()
        {
            m_departments = new List<Department>(){  new Department(1,"Dnepropetrovsk"),
                                                     new Department(2,"Kiev"),
                                                     new Department(3,"Lvov"),
                                                     new Department(4,"Donesck"),
                                                     new Department(5,"Harkov"),
                                                     new Department(6,"Odessa"),
                                                     new Department(7,"Hacapetovka"),
                                                     new Department(8,"Sevastopol"),
                                                     new Department(9,"Yalta"),
                                                     new Department(10,"Simferopol"),
                                                     new Department(11,"Alyshta")};
            m_employees = new List<Employee>() {   new Employee(1,"Dyadka","Dyadkovich",4),
                                                   new Employee(2,"Tetka","Tetkovna",1),
                                                   new Employee(3,"Makar","Makarovich",5),
                                                   new Employee(4,"Alexandr","Alexandrovich",7),
                                                   new Employee(5,"Andrey","Andreevich",3),
                                                   new Employee(6,"Andrey","Andreevich",3),
                                                   new Employee(7,"Vasiliy","sdfg",9),
                                                   new Employee(8,"Vasiliy","sdf",8),
                                                   new Employee(9,"Vasiliy","poi",7),
                                                   new Employee(10,"Vasiliy","wedf",5),
                                                   new Employee(11,"Vasiliy","mnbv",6),
                                                   new Employee(12,"Vasiliy","qwerfg",4),
                                                   new Employee(13,"Vasiliy","edfv",3),
                                                   new Employee(14,"Vasiliy","asdf",2),
                                                   new Employee(15,"Vasiliy","yhgb ",1),
                                                   new Employee(16,"Vasiliy","Alexandrovich",9),
                                                   new Employee(17,"Vasiliy","zxd",8),
                                                   new Employee(18,"Vasiliy","kjhb",7),
                                                   new Employee(19,"Vasiliy","ASFG",7),
                                                   new Employee(20,"Vasiliy","DGFGH",7),
                                                   new Employee(21,"Vasiliy","POEW",7),
                                                   new Employee(22,"Vasiliy","QSSC",7),
                                                   new Employee(23,"Vasiliy","PLNM",7),
                                                   new Employee(24,"Vasiliy","FGHRT",7),
                                                   new Employee(25,"Vasiliy","PLKJHGF",7),
                                                   new Employee(26,"Vasiliy","QSDFBN",7),
                                                   new Employee(27,"Vasiliy","QWDSD",7),
                                                   new Employee(29,"Vasiliy","THJ",8),
                                                   new Employee(30,"Vasiliy","VBN",8),
                                                   new Employee(31,"Vasiliy","YJK",8),
                                                   new Employee(32,"Vasiliy","JKA",8),
                                                   new Employee(33,"Vasiliy","SFGH",8),
                                                   new Employee(34,"Vasiliy","LOIUY",8),
                                                   new Employee(35,"Vasiliy","ASD",8),
                                                   new Employee(36,"Vasiliy","mnbv",8),
                                                   new Employee(37,"Vasiliy","XCV ",8),
                                                   new Employee(38,"Vasiliy","EDX",8),
                                                   new Employee(39,"Vasiliy","JHGF",8),
                                                   new Employee(40,"Vasiliy","ZXCT",8),
                                                   new Employee(41,"Vasiliy","edfv",3)};
        }

        #endregion

        #region Logic

        /// <summary>
        ///  Selects all department's cities
        /// </summary>
        public static void Linq1()
        {
            var cities = from city in m_departments
                         select city.City;

            Console.WriteLine("-1--------------Cities--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Selects list of employee's names without repeats and sorted by alphabet.
        /// </summary>
        public static void Linq2()
        {
            var names = (from name in m_employees
                         orderby name.FirstName
                         select name.FirstName).Distinct();

            Console.WriteLine("-2--------------Names--------------");
            foreach (var item in names)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Calulates amount of employees in certain department
        /// </summary>
        /// <param name="deptId">department's number</param>
        public static void Linq3(int deptId)
        {
            var amount = m_employees.Where(employee => (employee.DeptID == deptId)).Select(name => name.FirstName).Count();

            Console.WriteLine("-3--------------All employees from 3d department--------------");
            Console.WriteLine(amount);
        }

        /// <summary>
        /// Gets list departments with amount of employees. Sorted by amount of employees.
        /// </summary>
        public static void Linq4()
        {
            var IDandAmount = (from employee in m_employees
                               group employee by employee.DeptID into dept
                               orderby dept.Count() descending
                               select new { ID = dept.Key, Amount = dept.Count() }).ToList();

            Console.WriteLine("-4--------------Department ID | People amount in department--------------");
            foreach (var item in IDandAmount)
            {
                Console.WriteLine("{0} | {1}", item.ID, item.Amount);
            }
        }

        /// <summary>
        /// Gets list departments with amount of employees. Sorted by department city.
        /// </summary>
        public static void Linq5()
        {
            var depts = (from employee in m_employees
                         join department in m_departments on employee.DeptID equals department.ID
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).ToList();

            Console.WriteLine("-5-------------- City | People amount in department--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Selects list of cities, which contain leter 'D' at the beginig of the word.
        /// </summary>
        public static void Linq6()
        {
            var cities = m_departments.Where(city => city.City.StartsWith("D")).Select(city => city.City);

            Console.WriteLine("-6--------------Cities started with D--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Selects list of cities, which contain leter 'l' at the middle of the word and amount of employees
        /// </summary>
        public static void Linq7()
        {
            var cities =
                (from department in m_departments
                 join employee in m_employees on department.ID equals employee.DeptID into lj
                 from rows in lj.DefaultIfEmpty()
                 where department.City.IndexOf('l') > 0 && department.City.IndexOf('l') < department.City.Length - 1
                 orderby department.City
                 let EmplID = rows == null ? 0 : 1
                 select new { City = department.City, EmplID }).GroupBy(a => a.City)
                 .Select(item => new { City = item.Key, Amount = item.Sum(a => a.EmplID) })
                 .ToDictionary(dept => dept.City, dept => dept.Amount);

            Console.WriteLine("-7-------------- City with 'l' in the name | People amount in department-------");
            foreach (var item in cities)
            {
                Console.WriteLine("{0} | {1} ", item.Key, item.Value);
            }
        }

        /// <summary>
        /// Selects list of person with the same surname. Sorted by alphabet.
        /// </summary>
        public static void Linq8()
        {
            var namesGroups = (from name in m_employees
                               from name2 in m_employees
                               where name.ID != name2.ID && name.LastName == name2.LastName
                               orderby name.LastName, name.FirstName
                               select new Employee { ID = name.ID, FirstName = name.FirstName, LastName = name.LastName, DeptID = name.DeptID }).Distinct();

            Console.WriteLine("8---------------Same SurNames--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine("ID:{0} Name:{1} Surname:{2} DeptID:{3}", item.ID, item.FirstName, item.LastName, item.DeptID);
            }
        }

        /// <summary>
        /// Selects people with thw same name and surname from Lvov. Sorted by name
        /// </summary>
        public static void Linq9()
        {
            var namesGroups = ((from name in m_employees
                                from name2 in m_employees
                                where name.ID != name2.ID && name.FirstName == name2.FirstName && name.LastName == name2.LastName && name.DeptID == name2.DeptID
                                orderby name.FirstName
                                select new Employee { ID = name.ID, FirstName = name.FirstName, LastName = name.LastName, DeptID = name.DeptID })
                                .Where(d => d.DeptID == m_departments.Where(a => a.City == "Lvov")
                                .Select(a => new { ID = a.ID }).First().ID)).Distinct();

            Console.WriteLine("-9--------------Same full names--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine("ID:{0} Name:{1} Surname:{2} DeptID:{3}", item.ID, item.FirstName, item.LastName, item.DeptID);
            }
        }

        /// <summary>
        /// Selects list of cities with amount of employees named Vasiliy more than 10. Sorted by city
        /// </summary>
        public static void Linq10()
        {
            var depts = (from employee in m_employees
                         join department in m_departments on employee.DeptID equals department.ID
                         where employee.FirstName == "Vasiliy"
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).Where(d => d.Amount > 10).ToDictionary(dept => dept.City, dept => dept.Amount);


            Console.WriteLine("-10-------------- City | People amount in department (Vasiliy)--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.Key, item.Value);
            }
        }

        #endregion

        #region Fields

        private static List<Department> m_departments;
        private static List<Employee> m_employees;

        #endregion
    }
}
